# -*- coding: utf-8 -*-
import pymysql
from datetime import date, timedelta

from dbutils.pooled_db import PooledDB

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def subTableBySerId(serId,serName):
    tomorrow = (date.today() + timedelta(days=0)).strftime("%y%m%d")
    tableName = f"log_data_{serId}_{tomorrow}"
    createTableDdl1 = f"""CREATE TABLE IF NOT EXISTS `{tableName}` (
      `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `log_id` varchar(65) DEFAULT NULL COMMENT '每次提交的log_id',
      `active_id` varchar(45) DEFAULT NULL COMMENT '激活id',
      `key` varchar(50) DEFAULT NULL COMMENT '日志项名称',
      `msg` json DEFAULT NULL COMMENT 'data内容,是一个json',
      `log_time` bigint(10) DEFAULT NULL COMMENT '提交日志时间,毫秒',
      `os_boot_time` int(10) DEFAULT NULL COMMENT '单位秒，开机时长',
      `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`),
      KEY `idx_active_id` (`active_id`) USING BTREE,
      KEY `idx_key` (`key`) USING BTREE
    ) ENGINE=InnoDB COMMENT='生活助手用户行为表-{serName}'"""

    conn,cur = getDataConnection()
    cur.execute(createTableDdl1)
    conn.commit()

    sql = f"select count(1) from {tableName}"
    cur.execute(sql)
    res = cur.fetchone()
    print(createTableDdl1)
    print(f"------------------表{tableName}数据量为 {res[0]} --------------------------")
    cur.close()
    conn.close()

def subTableMain():
    subTableBySerId('common','公共日志')
    subTableBySerId('zjkj', '早间开机')
    subTableBySerId('yjyl', '夜间音量')
    subTableBySerId('jkzx', '健康作息')
    subTableBySerId('zjtx', '追剧提醒')

def subTableCommon():
    tomorrow = (date.today() + timedelta(days=1)).strftime("%y%m%d")
    tableName = f"log_common_service_{tomorrow}"
    createTableDdl1 = f"""CREATE TABLE IF NOT EXISTS `{tableName}` (
      `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
      `log_id` varchar(65) DEFAULT NULL COMMENT '每次提交的log_id',
      `active_id` varchar(45) DEFAULT NULL COMMENT '激活id',
      `key` varchar(50) DEFAULT NULL COMMENT '日志项名称',
      `msg` json DEFAULT NULL COMMENT 'data内容,是一个json',
      `log_time` bigint(10) DEFAULT NULL COMMENT '提交日志时间,毫秒',
      `os_boot_time` int(10) DEFAULT NULL COMMENT '单位秒，开机时长',
      `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`id`),
      KEY `idx_active_id` (`active_id`) USING BTREE,
      KEY `idx_key` (`key`) USING BTREE
    ) ENGINE=InnoDB COMMENT='生活助手用户行为表-通用日志表'"""

    conn,cur = getDataConnection()
    cur.execute(createTableDdl1)
    conn.commit()

    sql = f"select count(1) from {tableName}"
    cur.execute(sql)
    res = cur.fetchone()
    print(createTableDdl1)
    print(f"------------------表{tableName}数据量为 {res[0]} --------------------------")
    cur.close()
    conn.close()

def addColunm():
    createTableDdl1 = f""" ALTER TABLE `log_common_service_230315` ADD COLUMN `aa_id` varchar(65) NULL DEFAULT NULL COMMENT '每次提交的aaid' AFTER `log_id`"""
    conn, cur = getDataConnection()
    cur.execute(createTableDdl1)
    conn.commit()
    cur.close()
    conn.close()

if __name__ == '__main__':
    #subTableMain()
    #subTableCommon()
    addColunm()